﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using DO = AdminCondominio.Dominio;

namespace AdminCondominio.Persistencia
{
    public class TipoAreaComunDAO
    {
        private SqlConnection _cnn;
        private string _CadenaCnn = @"Data Source=localhost;Initial Catalog=BdCondominio;Persist Security Info=True;User ID=sa;Password=";
        //private string _CadenaCnn = @"Data Source=localhost;Initial Catalog=BdCondominio;Persist Security Info=True;User ID=sa;Password=";

        public TipoAreaComunDAO()
        {
            _cnn = new SqlConnection(_CadenaCnn);
        }

        public int InserTipoAreaComun(DO.TipoAreaComun value)
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"INSERT INTO tipo_area_comun VALUES(@DesAreaComun)";
            _cmd.Parameters.Add("@DesAreaComun", SqlDbType.VarChar, 50).Value = value.DesAreaComun;
            _cmd.Connection.Open();
            int FilasAfec = _cmd.ExecuteNonQuery();
            _cnn.Close(); 
            _cmd.Dispose();
            return FilasAfec;
        }

        public List<DO.TipoAreaComun> ListarTipoAreaComun(String value)
        {
            try
            {
                //SqlConnection _cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);

                _cnn.Open();
                string _Query = @"SELECT [CoTiAreaComun],[DesAreaComun] FROM [tipo_area_comun] where [DesAreaComun] like '%" + value + "%'";
                SqlCommand _cmd = new SqlCommand(_Query, _cnn);
                SqlDataReader _dr = _cmd.ExecuteReader();
                List<DO.TipoAreaComun> _LisAreaComun = new List<DO.TipoAreaComun>();
                while (_dr.Read())
                {
                    DO.TipoAreaComun _Item = new DO.TipoAreaComun();
                    _Item.CoTipoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                    _Item.DesAreaComun = _dr.IsDBNull(1) ? string.Empty : _dr.GetString(1);
                    _LisAreaComun.Add(_Item);
                }
                _cnn.Close();
                return _LisAreaComun;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DO.TipoAreaComun SeleccionarTipoAreaComun(int value)
        {
            try
            {
                //SqlConnection _cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);

                _cnn.Open();
                string _Query = @"SELECT [CoTiAreaComun],[DesAreaComun] FROM [tipo_area_comun] where [CoTiAreaComun] = " + value;
                SqlCommand _cmd = new SqlCommand(_Query, _cnn);
                SqlDataReader _dr = _cmd.ExecuteReader();
                List<DO.TipoAreaComun> _LisAreaComun = new List<DO.TipoAreaComun>();
                DO.TipoAreaComun _Item= null;
                if (_dr.Read())
                {
                    _Item = new DO.TipoAreaComun();
                    _Item.CoTipoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                    _Item.DesAreaComun = _dr.IsDBNull(1) ? string.Empty : _dr.GetString(1);
                }
                _cnn.Close();
                return _Item;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public DO.TipoAreaComun OptenerTipoAreaComun(DO.TipoAreaComun value)
        {
            try
            {
                //SqlConnection _cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);

                _cnn.Open();
                string _Query = @"SELECT [CoTiAreaComun],[DesAreaComun] FROM [tipo_area_comun] WHERE [CoTiAreaComun]=" + value.CoTipoAreaComun;
                SqlCommand _cmd = new SqlCommand(_Query, _cnn);
                SqlDataReader _dr = _cmd.ExecuteReader();
                DO.TipoAreaComun _Item = null;
                if (_dr.Read())
                {
                    _Item = new DO.TipoAreaComun();
                    _Item.CoTipoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                    _Item.DesAreaComun = _dr.IsDBNull(1) ? string.Empty : _dr.GetString(1);
                }
                _cnn.Close();
                return _Item;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public int UpdateTipoAreaComun(DO.TipoAreaComun value)
        {
            String _query=@"UPDATE tipo_area_comun SET [DesAreaComun]='" + value.DesAreaComun + "' where [CoTiAreaComun]=" + value.CoTipoAreaComun;
            _cnn.Open();  
            SqlCommand _cmd = new SqlCommand(_query,_cnn);
            int FilasAfec = _cmd.ExecuteNonQuery();
            _cnn.Close();
            _cmd.Dispose();
            return FilasAfec;
        }

        public int DeleteTipoAreaComun(DO.TipoAreaComun value)
        {
            String _query = @"DELETE tipo_area_comun where [CoTiAreaComun]=" + value.CoTipoAreaComun;
            _cnn.Open();
            SqlCommand _cmd = new SqlCommand(_query, _cnn);
            int FilasAfec = _cmd.ExecuteNonQuery();
            _cnn.Close();
            _cmd.Dispose();
            return FilasAfec;
        }
    }
}